*********************************************************************************************

8/18/09:  ADDED 06 & 07 DATA
12/18/09:  ADDED 08 DATA
11/1/10:  ADDED 09 DATA
10/18/11:  ADDED 10 DATA
12/19/12:  ADDED 11 DATA
9/11/13:  ADDED 12 DATA
10/15/14:  ADDED 13 DATA
9/4/17:  ADDED 15 & 16 DATA
9/13/17:  ADDED 17 DATA
9/10/18:  ADDED 18 DATA


NOTE: WHEN ADDED 2011 DATA NOW GET 
      "WARNING: Multiple lengths were specified for the variable salaryx_ by input data set(s)".
      AND UNIX SAYS "EXIT" INSTEAD OF "DONE"
      IN THE LOG FILE.  THIS DOESN'T SEEM TO AFFECT ANYTHING.  SAME DEAL WITH farm_ncx & nonf_rmx 

*********************************************************************************************;

LIBNAME mymemb '~/jim/data/ce_memb';
LIBNAME alldat '~/jim/data';

*%INCLUDE "~/jim/gzipseq.mcr";

%include "~/jim/inc_cons_trends/include/infile.mcr";


*******  MEMBER FILES   **********;

filename memb801 pipe 'gunzip -c ~/jim/data/ce_memb/1980_81/08423-0002-Data.txt.gz';
filename memb802 pipe 'gunzip -c ~/jim/data/ce_memb/1980_81/08423-0006-Data.txt.gz';
filename memb803 pipe 'gunzip -c ~/jim/data/ce_memb/1980_81/08423-0010-Data.txt.gz';
filename memb804 pipe 'gunzip -c ~/jim/data/ce_memb/1980_81/08423-0014-Data.txt.gz';

filename memb811 pipe 'gunzip -c ~/jim/data/ce_memb/1980_81/08423-0018-Data.txt.gz';
filename memb812 pipe 'gunzip -c ~/jim/data/ce_memb/1980_81/08423-0022-Data.txt.gz';
filename memb813 pipe 'gunzip -c ~/jim/data/ce_memb/1980_81/08423-0026-Data.txt.gz';
filename memb814 pipe 'gunzip -c ~/jim/data/ce_memb/1980_81/08423-0030-Data.txt.gz';
filename memb815 pipe 'gunzip -c ~/jim/data/ce_memb/1980_81/08423-0034-Data.txt.gz';

filename memb821 pipe 'gunzip -c ~/jim/data/ce_memb/1982_83/08598-0002-Data.txt.gz';
filename memb822 pipe 'gunzip -c ~/jim/data/ce_memb/1982_83/08598-0006-Data.txt.gz';
filename memb823 pipe 'gunzip -c ~/jim/data/ce_memb/1982_83/08598-0010-Data.txt.gz';
filename memb824 pipe 'gunzip -c ~/jim/data/ce_memb/1982_83/08598-0014-Data.txt.gz';

filename memb831 pipe 'gunzip -c ~/jim/data/ce_memb/1982_83/08598-0018-Data.txt.gz';
filename memb832 pipe 'gunzip -c ~/jim/data/ce_memb/1982_83/08598-0022-Data.txt.gz';
filename memb833 pipe 'gunzip -c ~/jim/data/ce_memb/1982_83/08598-0026-Data.txt.gz';
filename memb834 pipe 'gunzip -c ~/jim/data/ce_memb/1982_83/08598-0030-Data.txt.gz';

filename memb841 pipe 'gunzip -c ~/jim/data/ce_memb/1984/08671-0002-Data.txt.gz';
filename memb842 pipe 'gunzip -c ~/jim/data/ce_memb/1984/08671-0006-Data.txt.gz';
filename memb843 pipe 'gunzip -c ~/jim/data/ce_memb/1984/08671-0010-Data.txt.gz';
filename memb844 pipe 'gunzip -c ~/jim/data/ce_memb/1984/08671-0014-Data.txt.gz';

filename memb851 pipe 'gunzip -c ~/jim/data/ce_memb/1985/08904-0002-Data.txt.gz';
filename memb852 pipe 'gunzip -c ~/jim/data/ce_memb/1985/08904-0006-Data.txt.gz';
filename memb853 pipe 'gunzip -c ~/jim/data/ce_memb/1985/08904-0010-Data.txt.gz';
filename memb854 pipe 'gunzip -c ~/jim/data/ce_memb/1985/08904-0014-Data.txt.gz';
filename memb855 pipe 'gunzip -c ~/jim/data/ce_memb/1985/08904-0018-Data.txt.gz';

filename memb861 pipe 'gunzip -c ~/jim/data/ce_memb/1986/09113-0002-Data.txt.gz';
filename memb862 pipe 'gunzip -c ~/jim/data/ce_memb/1986/09113-0006-Data.txt.gz';
filename memb863 pipe 'gunzip -c ~/jim/data/ce_memb/1986/09113-0010-Data.txt.gz';
filename memb864 pipe 'gunzip -c ~/jim/data/ce_memb/1986/09113-0014-Data.txt.gz';

filename memb871 pipe 'gunzip -c ~/jim/data/ce_memb/1987/09332-0002-Data.txt.gz';
filename memb872 pipe 'gunzip -c ~/jim/data/ce_memb/1987/09332-0006-Data.txt.gz';
filename memb873 pipe 'gunzip -c ~/jim/data/ce_memb/1987/09332-0010-Data.txt.gz';
filename memb874 pipe 'gunzip -c ~/jim/data/ce_memb/1987/09332-0014-Data.txt.gz';

filename memb881 pipe 'gunzip -c ~/jim/data/ce_memb/1988/09842-0002-Data.txt.gz';
filename memb882 pipe 'gunzip -c ~/jim/data/ce_memb/1988/09842-0004-Data.txt.gz';
filename memb883 pipe 'gunzip -c ~/jim/data/ce_memb/1988/09842-0006-Data.txt.gz';
filename memb884 pipe 'gunzip -c ~/jim/data/ce_memb/1988/09842-0008-Data.txt.gz';

filename memb891 pipe 'gunzip -c ~/jim/data/ce_memb/1989/09841-0002-Data.txt.gz';
filename memb892 pipe 'gunzip -c ~/jim/data/ce_memb/1989/09841-0004-Data.txt.gz';
filename memb893 pipe 'gunzip -c ~/jim/data/ce_memb/1989/09841-0006-Data.txt.gz';
filename memb894 pipe 'gunzip -c ~/jim/data/ce_memb/1989/09841-0008-Data.txt.gz';

filename memb901 pipe 'gunzip -c ~/jim/data/ce_memb/1990/09817-0002-Data.txt.gz';
filename memb902 pipe 'gunzip -c ~/jim/data/ce_memb/1990/09817-0004-Data.txt.gz';
filename memb903 pipe 'gunzip -c ~/jim/data/ce_memb/1990/09817-0006-Data.txt.gz';
filename memb904 pipe 'gunzip -c ~/jim/data/ce_memb/1990/09817-0008-Data.txt.gz';

filename memb911 pipe 'gunzip -c ~/jim/data/ce_memb/1991/06262-0002-Data.txt.gz';
filename memb912 pipe 'gunzip -c ~/jim/data/ce_memb/1991/06262-0004-Data.txt.gz';
filename memb913 pipe 'gunzip -c ~/jim/data/ce_memb/1991/06262-0006-Data.txt.gz';
filename memb914 pipe 'gunzip -c ~/jim/data/ce_memb/1991/06262-0008-Data.txt.gz';

filename memb921 pipe 'gunzip -c ~/jim/data/ce_memb/1992/06440-0002-Data.txt.gz';
filename memb922 pipe 'gunzip -c ~/jim/data/ce_memb/1992/06440-0004-Data.txt.gz';
filename memb923 pipe 'gunzip -c ~/jim/data/ce_memb/1992/06440-0006-Data.txt.gz';
filename memb924 pipe 'gunzip -c ~/jim/data/ce_memb/1992/06440-0008-Data.txt.gz';

filename memb931 pipe 'gunzip -c ~/jim/data/ce_memb/1993/06543-0002-Data.txt.gz';
filename memb932 pipe 'gunzip -c ~/jim/data/ce_memb/1993/06543-0004-Data.txt.gz';
filename memb933 pipe 'gunzip -c ~/jim/data/ce_memb/1993/06543-0006-Data.txt.gz';
filename memb934 pipe 'gunzip -c ~/jim/data/ce_memb/1993/06543-0008-Data.txt.gz';

filename memb941 pipe 'gunzip -c ~/jim/data/ce_memb/1994/06710-0002-Data.txt.gz';
filename memb942 pipe 'gunzip -c ~/jim/data/ce_memb/1994/06710-0006-Data.txt.gz';
filename memb943 pipe 'gunzip -c ~/jim/data/ce_memb/1994/06710-0010-Data.txt.gz';
filename memb944 pipe 'gunzip -c ~/jim/data/ce_memb/1994/06710-0014-Data.txt.gz';

filename memb951 pipe 'gunzip -c ~/jim/data/ce_memb/1995/02264-0002-Data.txt.gz';
filename memb952 pipe 'gunzip -c ~/jim/data/ce_memb/1995/02264-0006-Data.txt.gz';
filename memb953 pipe 'gunzip -c ~/jim/data/ce_memb/1995/02264-0010-Data.txt.gz';
filename memb954 pipe 'gunzip -c ~/jim/data/ce_memb/1995/02264-0014-Data.txt.gz';
filename memb955 pipe 'gunzip -c ~/jim/data/ce_memb/1995/02264-0018-Data.txt.gz';

filename memb961 pipe 'gunzip -c ~/jim/data/ce_memb/1996/02794-0002-Data.txt.gz';
filename memb962 pipe 'gunzip -c ~/jim/data/ce_memb/1996/02794-0006-Data.txt.gz';
filename memb963 pipe 'gunzip -c ~/jim/data/ce_memb/1996/02794-0010-Data.txt.gz';
filename memb964 pipe 'gunzip -c ~/jim/data/ce_memb/1996/02794-0014-Data.txt.gz';

filename memb971 pipe 'gunzip -c ~/jim/data/ce_memb/1997/da2838.memb971.gz';
filename memb972 pipe 'gunzip -c ~/jim/data/ce_memb/1997/da2838.memb972.gz';
filename memb973 pipe 'gunzip -c ~/jim/data/ce_memb/1997/da2838.memb973.gz';
filename memb974 pipe 'gunzip -c ~/jim/data/ce_memb/1997/da2838.memb974.gz';

filename memb981 pipe 'gunzip -c ~/jim/data/ce_memb/1998/da2971.memb981.gz';
filename memb982 pipe 'gunzip -c ~/jim/data/ce_memb/1998/da2971.memb982.gz';
filename memb983 pipe 'gunzip -c ~/jim/data/ce_memb/1998/da2971.memb983.gz';
filename memb984 pipe 'gunzip -c ~/jim/data/ce_memb/1998/da2971.memb984.gz';

filename memb991 pipe 'gunzip -c ~/jim/data/ce_memb/1999/da3228.memb991.gz';
filename memb992 pipe 'gunzip -c ~/jim/data/ce_memb/1999/da3228.memb992.gz';
filename memb993 pipe 'gunzip -c ~/jim/data/ce_memb/1999/da3228.memb993.gz';
filename memb994 pipe 'gunzip -c ~/jim/data/ce_memb/1999/da3228.memb994.gz';

filename memb1001 pipe 'gunzip -c ~/jim/data/ce_memb/2000/da3396.memb001.gz';
filename memb1002 pipe 'gunzip -c ~/jim/data/ce_memb/2000/da3396.memb002.gz';
filename memb1003 pipe 'gunzip -c ~/jim/data/ce_memb/2000/da3396.memb003.gz';
filename memb1004 pipe 'gunzip -c ~/jim/data/ce_memb/2000/da3396.memb004.gz';

filename memb1011 pipe 'gunzip -c ~/jim/data/ce_memb/2001/03674-0002-Data.txt.gz';
filename memb1012 pipe 'gunzip -c ~/jim/data/ce_memb/2001/03674-0006-Data.txt.gz';
filename memb1013 pipe 'gunzip -c ~/jim/data/ce_memb/2001/03674-0010-Data.txt.gz';
filename memb1014 pipe 'gunzip -c ~/jim/data/ce_memb/2001/03674-0014-Data.txt.gz';

filename memb1021 pipe 'gunzip -c ~/jim/data/ce_memb/2002/03949-0002-Data.txt.gz';
filename memb1022 pipe 'gunzip -c ~/jim/data/ce_memb/2002/03949-0006-Data.txt.gz';
filename memb1023 pipe 'gunzip -c ~/jim/data/ce_memb/2002/03949-0010-Data.txt.gz';
filename memb1024 pipe 'gunzip -c ~/jim/data/ce_memb/2002/03949-0014-Data.txt.gz';

filename memb1031 pipe 'gunzip -c ~/jim/data/ce_memb/2003/membi031x.txt.gz';
filename memb1032 pipe 'gunzip -c ~/jim/data/ce_memb/2003/membi032.txt.gz';
filename memb1033 pipe 'gunzip -c ~/jim/data/ce_memb/2003/membi033.txt.gz';
filename memb1034 pipe 'gunzip -c ~/jim/data/ce_memb/2003/membi034.txt.gz';

filename memb1041 pipe 'gunzip -c ~/jim/data/ce_memb/2004/membi041x.txt.gz';
filename memb1042 pipe 'gunzip -c ~/jim/data/ce_memb/2004/membi042.txt.gz';
filename memb1043 pipe 'gunzip -c ~/jim/data/ce_memb/2004/membi043.txt.gz';
filename memb1044 pipe 'gunzip -c ~/jim/data/ce_memb/2004/membi044.txt.gz';
filename memb1045 pipe 'gunzip -c ~/jim/data/ce_memb/2004/membi051.txt.gz';

filename memb1051 pipe 'gunzip -c ~/jim/data/ce_memb/2005/membi051x.txt.gz';
filename memb1052 pipe 'gunzip -c ~/jim/data/ce_memb/2005/membi052.txt.gz';
filename memb1053 pipe 'gunzip -c ~/jim/data/ce_memb/2005/membi053.txt.gz';
filename memb1054 pipe 'gunzip -c ~/jim/data/ce_memb/2005/membi054.txt.gz';

filename memb1061 pipe 'gunzip -c ~/jim/data/ce_memb/2006/membi061x.txt.gz';
filename memb1062 pipe 'gunzip -c ~/jim/data/ce_memb/2006/membi062.txt.gz';
filename memb1063 pipe 'gunzip -c ~/jim/data/ce_memb/2006/membi063.txt.gz';
filename memb1064 pipe 'gunzip -c ~/jim/data/ce_memb/2006/membi064.txt.gz';

filename memb1071 pipe 'gunzip -c ~/jim/data/ce_memb/2007/membi071x.txt.gz';
filename memb1072 pipe 'gunzip -c ~/jim/data/ce_memb/2007/membi072.txt.gz';
filename memb1073 pipe 'gunzip -c ~/jim/data/ce_memb/2007/membi073.txt.gz';
filename memb1074 pipe 'gunzip -c ~/jim/data/ce_memb/2007/membi074.txt.gz';

filename memb1081 pipe 'gunzip -c ~/jim/data/ce_memb/2008/membi081x.txt.gz';
filename memb1082 pipe 'gunzip -c ~/jim/data/ce_memb/2008/membi082.txt.gz';
filename memb1083 pipe 'gunzip -c ~/jim/data/ce_memb/2008/membi083.txt.gz';
filename memb1084 pipe 'gunzip -c ~/jim/data/ce_memb/2008/membi084.txt.gz';

filename memb1091 pipe 'gunzip -c ~/jim/data/ce_memb/2009/membi091x.txt.gz';
filename memb1092 pipe 'gunzip -c ~/jim/data/ce_memb/2009/membi092.txt.gz';
filename memb1093 pipe 'gunzip -c ~/jim/data/ce_memb/2009/membi093.txt.gz';
filename memb1094 pipe 'gunzip -c ~/jim/data/ce_memb/2009/membi094.txt.gz';

filename memb1101 pipe 'gunzip -c ~/jim/data/ce_memb/2010/membi101x.txt.gz';
filename memb1102 pipe 'gunzip -c ~/jim/data/ce_memb/2010/membi102.txt.gz';
filename memb1103 pipe 'gunzip -c ~/jim/data/ce_memb/2010/membi103.txt.gz';
filename memb1104 pipe 'gunzip -c ~/jim/data/ce_memb/2010/membi104.txt.gz';


***************************************************************************
* MACRO FOR SURVEY YEARS 1980-2010
***************************************************************************;
%MACRO read(qyr,statement);

  DATA mb&qyr;  
   INFILE memb&qyr lrecl=600;
   INPUT &statement;
   IF &qyr IN (815,855,955,1045,1105) THEN ex_yr=1;
     ELSE ex_yr=0; 
   srv_year=FLOOR(&qyr/10)+ex_yr;
   qyear=&qyr;
   IF qyear LE 855 THEN newid=9000000+newid; 
  RUN;

  PROC SORT;
   BY newid membno; 
  RUN;

  PROC MEANS;
  RUN;

%MEND read;

***************************************************************************
* MACRO FOR SURVEY YEARS 2011 - 20132
***************************************************************************;
%MACRO read2(qyr,ext,yr4);

  PROC IMPORT DATAFILE="~/jim/data/ce_memb/&yr4/memi&ext..csv"
     OUT=raw_dat
     DBMS=CSV
     REPLACE;
     GETNAMES=YES;
  RUN;
 
  DATA mb&qyr;  
   SET raw_dat(RENAME=(cu_code=temp1 sex=temp2 marital=temp3 in_coll=temp4 earner=temp5 earntype=temp6 incnonwk=temp7 educa=temp8 horigin=temp9 membrace=temp10 newid=temp11)); 
 
   cu_code=temp1*1; 
   sex=temp2*1;
   marital=temp3*1; 
   in_coll=temp4*1; 
   earner=temp5*1; 
   earntype=temp6*1; 
   incnonwk=temp7*1; 
   educa=temp8*1; 
   horigin=temp9*1;
   membrace=temp10*1;
   newid=temp11*1;

   
   DROP temp1-temp11;
   
   IF &qyr IN (1145 1185) THEN ex_yr=1;
     ELSE ex_yr=0; 
   srv_year=FLOOR(&qyr/10)+ex_yr;
   qyear=&qyr;

KEEP  newid membno age cu_code sex marital in_coll earner earntype incnonwk incweekq inc_hrsq educa salaryx salaryx_ 
      farmincx farm_ncx nonfarmx nonf_rmx socrrx  ssix schmlwkq  schmlwkx horigin ex_yr srv_year qyear membrace;
RUN;

PROC MEANS; 
RUN; 

  PROC SORT;
   BY newid membno; 
  RUN;

%MEND read2;



***************************************************************************
* MACRO FOR SURVEY YEARS 20132 AND AFTER
* schmlwkx IS NO LONGER ASKED. WE NEVER USED IT
* farmincx nonfarmx AND THEIR FLAGS ARE REPLACED WITH SEMPFRMM
* some obs for SEMPFRMM in 171x have a '-' so can't convert to numeric

***************************************************************************;
%MACRO read3(qyr,ext,yr4);

  PROC IMPORT DATAFILE="~/jim/data/ce_memb/&yr4/memi&ext..csv"
     OUT=raw_dat
     DBMS=CSV
     REPLACE;
     GETNAMES=YES;
  RUN;
 
  DATA mb&qyr;  
   SET raw_dat(RENAME=(cu_code=temp1 sex=temp2 marital=temp3 in_coll=temp4 earner=temp5 earntype=temp6 incnonwk=temp7 educa=temp8 horigin=temp9 membrace=temp10 newid=temp11
			SEMPFRMM=temp12
			schmlwkq=temp13
			ssix=temp14)); 

   cu_code=temp1*1; 
   sex=temp2*1;
   marital=temp3*1; 
   in_coll=temp4*1; 
   earner=temp5*1; 
   earntype=temp6*1; 
   incnonwk=temp7*1; 
   educa=temp8*1; 
   horigin=temp9*1;
   membrace=temp10*1;
   newid=temp11*1;
   SEMPFRMM=temp12*1;
   schmlwkq=temp13*1;
   ssix=temp14*1; 
   
   IF &qyr IN (1145 1185) THEN ex_yr=1;
     ELSE ex_yr=0; 
   srv_year=FLOOR(&qyr/10)+ex_yr;
   qyear=&qyr;

KEEP  newid membno age cu_code sex marital in_coll earner earntype incnonwk incweekq inc_hrsq educa salaryx salaryx_ 
      socrrx  ssix schmlwkq  horigin ex_yr srv_year qyear SEMPFRMM SEMP_RMM membrace;
RUN;

PROC MEANS; 
RUN; 

  PROC SORT;
   BY newid membno; 
  RUN;

%MEND read3;






%read(801,&all8081);
%read(802,&all8081);
%read(803,&all8081);
%read(804,&all8081);

%read(811,&all8081);
%read(812,&all8081);
%read(813,&all8081);
%read(814,&all8081);
%read(815,&all8081);

%read(821,&all8285);
%read(822,&all8285);
%read(823,&all8285);
%read(824,&all8285);

%read(831,&all8285);
%read(832,&all8285);
%read(833,&all8285);
%read(834,&all8285);

%read(841,&all8285);
%read(842,&all8285);
%read(843,&all8285);
%read(844,&all8285);

%read(851,&all8285);
%read(852,&all8285);
%read(853,&all8285);
%read(854,&all8285);
%read(855,&all8285);

%read(861,&all8695);
%read(862,&all8695);
%read(863,&all8695);
%read(864,&all8695);

%read(871,&all8695);
%read(872,&all8695);
%read(873,&all8695);
%read(874,&all8695);

%read(881,&all8695);
%read(882,&all8695);
%read(883,&all8695);
%read(884,&all8695);

%read(891,&all8695);
%read(892,&all8695);
%read(893,&all8695);
%read(894,&all8695);

%read(901,&all8695);
%read(902,&all8695);
%read(903,&all8695);
%read(904,&all8695);

%read(911,&all8695);
%read(912,&all8695);
%read(913,&all8695);
%read(914,&all8695);

%read(921,&all8695);
%read(922,&all8695);
%read(923,&all8695);
%read(924,&all8695);

%read(931,&all8695);
%read(932,&all8695);
%read(933,&all8695);
%read(934,&all8695);

%read(941,&all8695);
%read(942,&all8695);
%read(943,&all8695);
%read(944,&all8695);

%read(951,&all8695);
%read(952,&all8695);
%read(953,&all8695);
%read(954,&all8695);
%read(955,&all8695);

%read(961,&all9603);
%read(962,&all9603);
%read(963,&all9603);
%read(964,&all9603);

%read(971,&all9603);
%read(972,&all9603);
%read(973,&all9603);
%read(974,&all9603);

%read(981,&all9603);
%read(982,&all9603);
%read(983,&all9603);
%read(984,&all9603);

%read(991,&all9603);
%read(992,&all9603);
%read(993,&all9603);
%read(994,&all9603);

%read(1001,&all9603);
%read(1002,&all9603);
%read(1003,&all9603);
%read(1004,&all9603);

%read(1011,&all9603);
%read(1012,&all9603);
%read(1013,&all9603);
%read(1014,&all9603);

%read(1021,&all9603);
%read(1022,&all9603);
%read(1023,&all9603);
%read(1024,&all9603);

%read(1031,&all9603);
%read(1032,&all9603_2);
%read(1033,&all9603_2);
%read(1034,&all9603_2);

%read(1041,&all04);
%read(1042,&all04);
%read(1043,&all04);
%read(1044,&all04);
%read(1045,&all04);

%read(1051,&all04);
%read(1052,&all04);
%read(1053,&all04);
%read(1054,&all04);

%read(1061,&all04);
%read(1062,&all04);
%read(1063,&all04);
%read(1064,&all04);

%read(1071,&all04);
%read(1072,&all04);
%read(1073,&all04);
%read(1074,&all04);

%read(1081,&all04);
%read(1082,&all04);
%read(1083,&all04);
%read(1084,&all04);

%read(1091,&all04);
%read(1092,&all04);
%read(1093,&all04);
%read(1094,&all04);

%read(1101,&all04);
%read(1102,&all04);
%read(1103,&all04);
%read(1104,&all04);

%read2(1111,111x,2011);
%read2(1112,112,2011);
%read2(1113,113,2011);
%read2(1114,114,2011);

%read2(1121,121x,2012);
%read2(1122,122,2012);
%read2(1123,123,2012);
%read2(1124,124,2012);

%read2(1131,131x,2013);

%read3(1132,132,2013);
%read3(1133,133,2013);
%read3(1134,134,2013);

%read3(1141,141x,2014);
%read3(1142,142,2014);
%read3(1143,143,2014);
%read3(1144,144,2014);
%read3(1145,151,2014);

%read3(1151,151x,2015);
%read3(1152,152,2015);
%read3(1153,153,2015);
%read3(1154,154,2015);

%read3(1161,161x,2016);
%read3(1162,162,2016);
%read3(1163,163,2016);
%read3(1164,164,2016);

* %read3(1171,171x,2017);
%read3(1172,172,2017);
%read3(1173,173,2017);
%read3(1174,174,2017);

%read3(1181,181x,2018);
%read3(1182,182,2018);
%read3(1183,183,2018);
%read3(1184,184,2018);
%read3(1185,191,2018);

*********************************************************
RUN 1171 SEPARATELY, BECAUSE SEMPFRMM IS CODED AS A DIFF
TYPE IN THE 2017 DATA, AND THAT LEADS TO AN ERR, SO TAKE
THAT VARIABLE FROM THE 2016 Q5 DATA. 
*********************************************************;
  PROC IMPORT DATAFILE="~/jim/data/ce_memb/2016/memi171.csv"
     OUT=raw_dat1
     DBMS=CSV
     REPLACE;
     GETNAMES=YES;
  RUN;
  PROC IMPORT DATAFILE="~/jim/data/ce_memb/2017/memi171x.csv"
     OUT=raw_dat
     DBMS=CSV
     REPLACE;
     GETNAMES=YES;
  RUN;

data raw_dat1;
set raw_dat1(KEEP = newid membno SEMPFRMM RENAME= (newid=temp11 SEMPFRMM=temp12));
   newid=temp11*1;
   SEMPFRMM=temp12*1;
drop temp11 temp12;
RUN;
  PROC SORT;
   BY newid membno; 
  RUN;

DATA mb1171;  
   SET raw_dat(DROP = SEMPFRMM RENAME=(cu_code=temp1 sex=temp2 marital=temp3 in_coll=temp4 earner=temp5 earntype=temp6 incnonwk=temp7 educa=temp8 horigin=temp9 membrace=temp10 newid=temp11
			schmlwkq=temp13
			ssix=temp14)); 

   cu_code=temp1*1; 
   sex=temp2*1;
   marital=temp3*1; 
   in_coll=temp4*1; 
   earner=temp5*1; 
   earntype=temp6*1; 
   incnonwk=temp7*1; 
   educa=temp8*1; 
   horigin=temp9*1;
   membrace=temp10*1;
   newid=temp11*1;
   schmlwkq=temp13*1;
   ssix=temp14*1; 
   
   ex_yr=0; 
   srv_year=117;
   qyear=1171;

KEEP  newid membno age cu_code sex marital in_coll earner earntype incnonwk incweekq inc_hrsq educa salaryx salaryx_ 
      socrrx  ssix schmlwkq  horigin ex_yr srv_year qyear SEMP_RMM membrace;
RUN;

PROC MEANS; 
RUN; 

  PROC SORT;
   BY newid membno; 
  RUN;


DATA mb1171;
   MERGE mb1171(IN=in1) raw_dat1;
   BY newid membno; 
   IF in1;
RUN;
PROC MEANS;
RUN;




******* ADJUST*******************************************
MERGE TO GET RID OF ANY DUPLICATES IN 855 AND 861, ETC
*********************************************************;

DATA membdat;
MERGE
mb801 mb802 mb803 mb804
mb811 mb812 mb813 mb814 mb815
mb821 mb822 mb823 mb824
mb831 mb832 mb833 mb834
mb841 mb842 mb843 mb844
mb851 mb852 mb853 mb854 mb855
mb861 mb862 mb863 mb864
mb871 mb872 mb873 mb874
mb881 mb882 mb883 mb884
mb891 mb892 mb893 mb894
mb901 mb902 mb903 mb904
mb911 mb912 mb913 mb914
mb921 mb922 mb923 mb924
mb931 mb932 mb933 mb934
mb941 mb942 mb943 mb944
mb951 mb952 mb953 mb954 mb955
mb961 mb962 mb963 mb964
mb971 mb972 mb973 mb974
mb981 mb982 mb983 mb984 
mb991 mb992 mb993 mb994
mb1001 mb1002 mb1003 mb1004
mb1011 mb1012 mb1013 mb1014
mb1021 mb1022 mb1023 mb1024
mb1031 mb1032 mb1033 mb1034 
mb1041 mb1042 mb1043 mb1044 mb1045
mb1051 mb1052 mb1053 mb1054 
mb1061 mb1062 mb1063 mb1064 
mb1071 mb1072 mb1073 mb1074
mb1081 mb1082 mb1083 mb1084 
mb1091 mb1092 mb1093 mb1094 
mb1101 mb1102 mb1103 mb1104
mb1111 mb1112 mb1113 mb1114 
mb1121 mb1122 mb1123 mb1124 
mb1131 mb1132 mb1133 mb1134 
mb1141 mb1142 mb1143 mb1144 mb1145
mb1151 mb1152 mb1153 mb1154 
mb1161 mb1162 mb1163 mb1164 
mb1171 mb1172 mb1173 mb1174
mb1181 mb1182 mb1183 mb1184 mb1185;

BY newid membno;

RUN;


********************************************************************************
* CREATE PERMANENT SAS DATASET TO BE USED BY TIME USE PROGRAMS
********************************************************************************;

DATA alldat.memdat_for_tu80_18;
   SET membdat;
RUN;
PROC MEANS DATA=alldat.memdat_for_tu80_18;
RUN;


PROC SORT DATA=alldat.memdat_for_tu80_18;
BY newid;
RUN;
PROC SORT DATA=alldat.memdat_for_tu80_17;
BY newid;
RUN;

PROC COMPARE BASE=alldat.memdat_for_tu80_18
	     COMPARE=alldat.memdat_for_tu80_17;
   WHERE qyear LE 1174;
RUN;


************************************************************************;

DATA membdat;
SET membdat;

IF 1 LE incnonwk LE 6 THEN nowork=1;
   ELSE nowork=0;
IF incnonwk=5 AND 801 LE qyear LE 934 THEN retire=1;
   ELSE IF incnonwk=1 AND qyear GE 941 THEN retire=1;
   ELSE retire=0;


***********************************************
CREATE AGE-SEX-MARITAL STATUS (ASM) DUMMIES
***********************************************;
IF 65 LE age LE 69 AND sex=1 AND marital=1 THEN asm_grp=1;
   ELSE IF 65 LE age LE 69 AND sex=1 AND  marital GT 1 THEN asm_grp=2;
   ELSE IF 65 LE age LE 69 AND sex=2 AND  marital=1 THEN asm_grp=3;
   ELSE IF 65 LE age LE 69 AND sex=2 AND  marital GT 1 THEN asm_grp=4;

   ELSE IF 70 LE age LE 74 AND sex=1 AND  marital=1 THEN asm_grp=5;
   ELSE IF 70 LE age LE 74 AND sex=1 AND  marital GT 1 THEN asm_grp=6;
   ELSE IF 70 LE age LE 74 AND sex=2 AND  marital=1 THEN asm_grp=7;
   ELSE IF 70 LE age LE 74 AND sex=2 AND  marital GT 1 THEN asm_grp=8;

   ELSE IF 75 LE age LE 79 AND sex=1 AND  marital=1 THEN asm_grp=9;
   ELSE IF 75 LE age LE 79 AND sex=1 AND  marital GT 1 THEN asm_grp=10;
   ELSE IF 75 LE age LE 79 AND sex=2 AND  marital=1 THEN asm_grp=11;
   ELSE IF 75 LE age LE 79 AND sex=2 AND  marital GT 1 THEN asm_grp=12;

   ELSE IF age GE 80 AND sex=1 AND  marital=1 THEN asm_grp=13;
   ELSE IF age GE 80 AND sex=1 AND  marital GT 1 THEN asm_grp=14;
   ELSE IF age GE 80 AND sex=2 AND  marital=1 THEN asm_grp=15;
   ELSE IF age GE 80 AND sex=2 AND  marital GT 1 THEN asm_grp=16;

   ELSE asm_grp=17;

ARRAY asm_dum(1:16);
DO t=1 TO 16;
   IF asm_grp=t THEN asm_dum(t)=1;
     ELSE asm_dum(t)=0;
END;



****************************************************************
CREATE # OF KIDS FOR WIC, SCHL LUNCH PROGRAM ELIG. FOR SPM PAPER
****************************************************************;
* EXCLUDE REF PERSON AND SPOUSE FROM KID COUNT;
IF 4 LE age LE 18 AND 3 LE cu_code LE 9 THEN num_kids_4_18=1; 
   ELSE num_kids_4_18=0;
IF 0 LE age LE 4 THEN num_kids_0_4=1; 
   ELSE  num_kids_0_4=0;
IF age=0 THEN num_kids_0=1; 
   ELSE  num_kids_0=0;


****************************************************************
IDENTIFY WOMEN WHO COULD POTENTIALLY BE ELIGIBLE FOR WIC
****************************************************************;
IF 15 LE age LE 45 AND sex=2 THEN pot_wic_mom=1;
   ELSE pot_wic_mom=0;

****************************************************************
IDENTIFY # OF KIDS PAYING FOR SCHOOL MEALS, WHICH WILL BE USED TO
DETERMINE VALUE OF REDUCED PRICE MEALS FOR CU
****************************************************************;
IF schmlwkq GT 0 THEN num_reduce_meal=1;
   ELSE num_reduce_meal=0;


***********************************************
CREATE SEX-MARITAL STATUS-RETIRE DUMMIES
***********************************************;
IF 65 LE age LE 74 AND sex=1 AND marital=1 AND nowork=1 THEN nowk_grp=1;
   ELSE IF 65 LE age LE 74 AND sex=1 AND marital GT 1 AND nowork=1 THEN nowk_grp=2;
   ELSE IF 65 LE age LE 74 AND sex=2 AND marital=1 AND nowork=1 THEN nowk_grp=3;
   ELSE IF 65 LE age LE 74 AND sex=2 AND marital GT 1 AND nowork=1 THEN nowk_grp=4;

   ELSE IF age GE 75 AND sex=1 AND marital=1 AND nowork=1 THEN nowk_grp=5;
   ELSE IF age GE 75 AND sex=1 AND marital GT 1 AND nowork=1 THEN nowk_grp=6;
   ELSE IF age GE 75 AND sex=2 AND marital=1 AND nowork=1 THEN nowk_grp=7;
   ELSE IF age GE 75 AND sex=2 AND marital GT 1 AND nowork=1 THEN nowk_grp=8;

   ELSE nowk_grp=9;

IF 65 LE age LE 74 AND sex=1 AND marital=1 AND retire=1 THEN ret_grp=1;
   ELSE IF 65 LE age LE 74 AND sex=1 AND marital GT 1 AND retire=1 THEN ret_grp=2;
   ELSE IF 65 LE age LE 74 AND sex=2 AND marital=1 AND retire=1 THEN ret_grp=3;
   ELSE IF 65 LE age LE 74 AND sex=2 AND marital GT 1 AND retire=1 THEN ret_grp=4;

   ELSE IF age GE 75 AND sex=1 AND marital=1 AND retire=1 THEN ret_grp=5;
   ELSE IF age GE 75 AND sex=1 AND marital GT 1 AND retire=1 THEN ret_grp=6;
   ELSE IF age GE 75 AND sex=2 AND marital=1 AND retire=1 THEN ret_grp=7;
   ELSE IF age GE 75 AND sex=2 AND marital GT 1 AND retire=1 THEN ret_grp=8;

   ELSE ret_grp=9;

ARRAY nowk_dum(1:8);
ARRAY ret_dum(1:8);
DO t=1 TO 8;
   IF nowk_grp=t THEN nowk_dum(t)=1;
     ELSE nowk_dum(t)=0;
   IF ret_grp=t THEN ret_dum(t)=1;
     ELSE ret_dum(t)=0;
END;


***********************************************
CREATE IDENTIFIER FOR EXCLUSION OF INCOME NON-RESPONDERS
***********************************************;
IF qyear LE 1131 THEN DO;
	exclude=0; 
	IF cu_code IN(1,2) AND salaryx_="C" THEN exclude=1; 
	IF cu_code IN(1,2) AND farm_ncx="C" THEN exclude=1;
	IF cu_code IN(1,2) AND nonf_rmx="C" THEN exclude=1; 
	 
	IF cu_code IN(1,2) AND earner=1 
	  AND salaryx IN (0,.) 
	  AND farmincx IN (0,.) 
	  AND nonfarmx IN (0,.) THEN exclude=1;
END;

 
IF qyear GE 1132 THEN DO;
	exclude=0; 
	IF cu_code IN(1,2) AND salaryx_="C" THEN exclude=1; 
	IF cu_code IN(1,2) AND SEMP_RMM="C" THEN exclude=1;
	 
	IF cu_code IN(1,2) AND earner=1 
	  AND salaryx IN (0,.) 
	  AND SEMPFRMM IN (0,.) THEN exclude=1;
END;

topcode_inc=0; 
IF salaryx_="T" OR farm_ncx="T" OR nonf_rmx="T" OR SEMP_RMM="T" THEN topcode_inc=1; 

***********************************************
CREATE VARS FOR TAXSIM
***********************************************;
*NOTE, THIS APPLIES THE PERS. EXEMPTION FORM YEAR PRIOR TO SURVEY YEAR
 THE PERSONAL EXEMPTION WENT AWAY IN 2018 SO NOW USE THE INCOME TEST FOR
 QUALIFYING DEPENDENTS, WHICH WAS THE SAME AS THE PERSONAL EXEMPTION IN PAST;

*NOTE UESE PERSONAL EXEMPTION FROM YEAR PRIOR TO SRV_YEAR;
     
    IF 1980 LE (srv_year+1900) LE 1985 THEN inc_test=1000;  
        ELSE IF (srv_year+1900)=1986 THEN inc_test=1040; 
	      ELSE IF (srv_year+1900)=1987 THEN inc_test=1080; 
	      ELSE IF (srv_year+1900)=1988 THEN inc_test=1900; 
        ELSE IF (srv_year+1900)=1988 THEN inc_test=1900; 
        ELSE IF (srv_year+1900)=1989 THEN inc_test=1950;   
        ELSE IF (srv_year+1900)=1990 THEN inc_test=2000;   
        ELSE IF (srv_year+1900)=1991 THEN inc_test=2050;
        ELSE IF (srv_year+1900)=1992 THEN inc_test=2150;
        ELSE IF (srv_year+1900)=1993 THEN inc_test=2300;
        ELSE IF (srv_year+1900)=1994 THEN inc_test=2350;
        ELSE IF (srv_year+1900)=1995 THEN inc_test=2450;
        ELSE IF (srv_year+1900)=1996 THEN inc_test=2500;
        ELSE IF (srv_year+1900)=1997 THEN inc_test=2550;
        ELSE IF (srv_year+1900)=1998 THEN inc_test=2650;
        ELSE IF (srv_year+1900)=1999 THEN inc_test=2700; 
        ELSE IF (srv_year+1900)=2000 THEN inc_test=2750;
        ELSE IF (srv_year+1900)=2001 THEN inc_test=2800;
        ELSE IF (srv_year+1900)=2002 THEN inc_test=2900;
        ELSE IF (srv_year+1900)=2003 THEN inc_test=3000; 
        ELSE IF (srv_year+1900)=2004 THEN inc_test=3050; 
        ELSE IF (srv_year+1900)=2005 THEN inc_test=3100; 
        ELSE IF (srv_year+1900)=2006 THEN inc_test=3200; 
				ELSE IF (srv_year+1900)=2007 THEN inc_test=3300; 
				ELSE IF (srv_year+1900)=2008 THEN inc_test=3400;
				ELSE IF (srv_year+1900)=2009 THEN inc_test=3500; 
				ELSE IF (srv_year+1900)=2010 THEN inc_test=3650; 
				ELSE IF (srv_year+1900)=2011 THEN inc_test=3650; 
				ELSE IF (srv_year+1900)=2012 THEN inc_test=3700; 
				ELSE IF (srv_year+1900)=2013 THEN inc_test=3800;
				ELSE IF (srv_year+1900)=2014 THEN inc_test=3900; 
				ELSE IF (srv_year+1900)=2015 THEN inc_test=3950;
				ELSE IF (srv_year+1900)=2016 THEN inc_test=4000;
				ELSE IF (srv_year+1900)=2017 THEN inc_test=4050;  
				ELSE IF (srv_year+1900)=2018 THEN inc_test=4050;  
				ELSE IF (srv_year+1900)=2019 THEN inc_test=4150;  

dep=0; 
* All non-heads, non-spouses under 19 are dependents;
if 3 LE cu_code LE 9 AND age LE 18 THEN dep=1;    
* All non-heads, non-spouses not married and not making $ are deps;
if 3 LE cu_code LE 9 AND marital GT 1 AND (earner=2 AND earntype=.) THEN dep=1;
* All non-heads, non-spouses not married and not making  alot of $
  and are going to school are deps; 
if 3 LE cu_code LE 9 AND marital GT 1 AND earntype NE 1
        AND in_coll IN(1,2) AND salaryx LT inc_test THEN dep=1;
* allow married non-working children 19-23 to be dependents;
IF 3 LE cu_code LE 9 AND marital=1 AND (earner=2 AND earntype=.)
        AND 19 LE age LE 23 THEN dep=1;
* allow married work little b/c of school 19-23 to be dependents;
IF 3 LE cu_code LE 9 AND marital=1 AND earntype NE 1
        AND in_coll IN (1,2) AND salaryx LT inc_test
        AND 19 LE age LE 23 THEN dep=1;
 
 
* calc num of taxpayers over 65; 
IF age GT 65 AND cu_code IN(1,2) THEN gt65=1;  
ELSE gt65=0; 
 
* calc num of kids for fed child tax credit; 
IF cu_code IN(3,4,6,8,9) AND age LE 16 THEN kid_fcc=1;
ELSE kid_fcc=0; 
         
* calc num of eitc kids; 
IF cu_code IN(3,4,6,8,9) AND age LE 18 THEN kid_eitc=1; 
ELSE IF cu_code IN(3,4,6,8,9) AND 19 LE age LE 23
                        AND in_coll IN (1,2) THEN kid_eitc=1; 
ELSE kid_eitc=0; 

***************************************
FOR GATES POVERTY RATES BY DEMOG GROUP
***************************************;
IF age LE 5 THEN under6=1;
   ELSE under6=0;
IF sex=1 THEN male=1;
   ELSE male=0;

IF (801 LE qyear LE 1031) THEN DO;
     IF originr=2 THEN hispanic=1;
       ELSE hispanic=0;
END;
IF qyear GE 1032 THEN DO;
     IF horigin=1 THEN hispanic=1;
       ELSE hispanic=0;
END;


IF (801 LE qyear LE 814) OR (881 LE qyear LE 1031) THEN DO;
	   IF race=1 THEN race_grp=1;
       ELSE IF race=2 THEN race_grp=2; 
       ELSE IF race=4 THEN race_grp=3;
       ELSE race_grp=4;
END;
IF (841 LE qyear LE 874) THEN DO;
	   IF race=1 THEN race_grp=1;
       ELSE IF race=2 THEN race_grp=2; 
       ELSE IF race=3 THEN race_grp=3;
       ELSE race_grp=4;
END;
IF qyear GE 1032 THEN DO;
		 IF membrace=1 THEN race_grp=1;
       ELSE IF membrace=2 THEN race_grp=2; 
       ELSE IF membrace=4 THEN race_grp=3;
       ELSE race_grp=4;
END;

ARRAY race_dum(1:4);
  DO t=1 TO 4;
     IF race_grp=t THEN race_dum(t)=1;
       ELSE race_dum(t)=0; 
END; 



RUN;

PROC DATASETS LIBRARY=work;
DELETE 
mb801 mb802 mb803 mb804 
mb811 mb812 mb813 mb814 mb815
mb821 mb822 mb823 mb824 
mb831 mb832 mb833 mb834 
mb841 mb842 mb843 mb844 
mb851 mb852 mb853 mb854 mb855
mb861 mb862 mb863 mb864 
mb871 mb872 mb873 mb874 
mb881 mb882 mb883 mb884
mb891 mb892 mb893 mb894
mb901 mb902 mb903 mb904 
mb911 mb912 mb913 mb914
mb921 mb922 mb923 mb924 
mb931 mb932 mb933 mb934 
mb941 mb942 mb943 mb944
mb951 mb952 mb953 mb954 mb955
mb961 mb962 mb963 mb964
mb971 mb972 mb973 mb974 
mb981 mb982 mb983 mb984 
mb991 mb992 mb993 mb994 
mb1001 mb1002 mb1003 mb1004 
mb1011 mb1012 mb1013 mb1014 
mb1021 mb1022 mb1023 mb1024 
mb1031 mb1032 mb1033 mb1034 
mb1041 mb1042 mb1043 mb1044 mb1045
mb1051 mb1052 mb1053 mb1054 
mb1061 mb1062 mb1063 mb1064 
mb1071 mb1072 mb1073 mb1074 
mb1081 mb1082 mb1083 mb1084 
mb1091 mb1092 mb1093 mb1094 
mb1101 mb1102 mb1103 mb1104
mb1111 mb1112 mb1113 mb1114 
mb1121 mb1122 mb1123 mb1124
mb1131 mb1132 mb1133 mb1134 
mb1141 mb1142 mb1143 mb1144 mb1145
mb1151 mb1152 mb1153 mb1154 
mb1161 mb1162 mb1163 mb1164 
mb1171 mb1172 mb1173 mb1174
mb1181 mb1182 mb1183 mb1184 mb1185;
RUN;

PROC MEANS DATA=membdat SUM NOPRINT;
  BY newid;
  ID srv_year qyear; 
  VAR asm_dum1-asm_dum16 nowk_dum1-nowk_dum8 ret_dum1-ret_dum8 exclude
      num_kids_4_18 num_kids_0_4 num_kids_0 pot_wic_mom num_reduce_meal incweekq
      dep gt65 kid_fcc kid_eitc topcode_inc under6 male race_dum1-race_dum4 hispanic;
  OUTPUT OUT=temp1(DROP=_TYPE_) SUM=;
RUN;

PROC MEANS DATA=temp1;
RUN;

PROC SORT DATA=membdat;
   BY newid qyear; 
RUN;
PROC MEANS DATA=membdat SUM NOPRINT;
  BY newid qyear; 
  ID srv_year; 
  VAR asm_dum1-asm_dum16 nowk_dum1-nowk_dum8 ret_dum1-ret_dum8 exclude
      num_kids_4_18 num_kids_0_4 num_kids_0 pot_wic_mom num_reduce_meal incweekq
      dep gt65 kid_fcc kid_eitc topcode_inc under6 male race_dum1-race_dum4 hispanic;
  OUTPUT OUT=memb_age_dat(DROP=_TYPE_) SUM=;
RUN; 

* IN 945 CASES THERE IS A REPEATED OBS FROM BOTH 815 AND 821.  THESE WEREN'T
  REMOVED IN THE MERGE PROCESS ABOVE BECAUSE MEMBNO DIDN'T LINE UP.  I REMOVE 
  THESE REPEATED OBS HERE.;

PROC SORT DATA=memb_age_dat;
  BY srv_year newid;
RUN;

DATA alldat.memb_age_dat80_18;
SET memb_age_dat;
  BY srv_year newid;

IF FIRST.srv_year OR FIRST.newid;

num_wic_mom=MIN(num_kids_0,pot_wic_mom);

RUN;

PROC MEANS DATA=alldat.memb_age_dat80_18;
RUN;

PROC SORT DATA=alldat.memb_age_dat80_18;
BY qyear;
RUN;
PROC MEANS DATA=alldat.memb_age_dat80_18;
BY qyear;
RUN;

PROC SORT DATA=alldat.memb_age_dat80_18;
BY newid;
RUN;
PROC SORT DATA=alldat.memb_age_dat80_17;
BY newid;
RUN;

PROC COMPARE BASE=alldat.memb_age_dat80_18
	     COMPARE=alldat.memb_age_dat80_17;
   WHERE qyear LE 1174;
RUN;

